"""
视频模型
映射数据库videos_detail视图
"""
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, DECIMAL, Index, func, text
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import relationship
from typing import Optional, List, Dict, Any, Tuple
import json

from app.models.base import BaseModel

# 导入Redis工具
from app.utils.redis_utils import RedisKeys, CacheExpiry, RedisUtils

# 创建RedisUtils实例
redis_utils = RedisUtils()


class Video(BaseModel):
    """
    视频模型类
    对应数据库中的videos_detail视图
    """
    
    __tablename__ = "videos_detail"
    
    id = Column(
        Integer, 
        primary_key=True, 
        autoincrement=True,
        comment='视频ID'
    )
    
    title = Column(
        String(255), 
        nullable=False,
        comment='视频标题'
    )
    
    description = Column(
        Text, 
        nullable=True,
        comment='视频描述'
    )
    
    cover_url = Column(
        String(255), 
        nullable=False,
        comment='封面图片URL'
    )
    
    video_url = Column(
        String(255), 
        nullable=False,
        comment='视频文件URL'
    )
    
    duration = Column(
        Integer, 
        nullable=False,
        comment='视频时长（秒）'
    )
    
    category_id = Column(
        Integer, 
        ForeignKey('categories.id'), 
        nullable=False,
        index=True,
        comment='分类ID'
    )
    
    user_id = Column(
        Integer, 
        ForeignKey('users.id'), 
        nullable=False,
        index=True,
        comment='上传用户ID'
    )
    
    view_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='观看次数'
    )
    
    like_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='点赞数'
    )
    
    comment_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='评论数'
    )
    
    # 关系定义
    user = relationship("User", lazy="joined")
    category = relationship("Category", lazy="joined")
    
    favorite_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='收藏数'
    )
    
    # 其他计数字段
    coin_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='投币数'
    )
    
    collect_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='收藏数'
    )
    
    share_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='分享数'
    )
    
    danmaku_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='弹幕数'
    )
    
    tags = Column(
        String(255),
        nullable=True,
        comment='标签'
    )
    
    actors = Column(
        String(255),
        nullable=True,
        comment='演员'
    )
    
    # 视图特有字段
    video_type_id = Column(
        Integer,
        nullable=True,
        comment='视频类型ID'
    )
    
    type_name = Column(
        String(50),
        nullable=True,
        comment='视频类型名称'
    )
    
    username = Column(
        String(50),
        nullable=True,
        comment='用户名'
    )
    
    nickname = Column(
        String(50),
        nullable=True,
        comment='用户昵称'
    )
    
    avatar = Column(
        String(255),
        nullable=True,
        comment='用户头像'
    )
    
    category_name = Column(
        String(50),
        nullable=True,
        comment='分类名称'
    )
    
    status = Column(
        TINYINT, 
        nullable=False, 
        default=1,
        comment='状态：1-审核通过，0-审核中，-1-审核拒绝'
    )
    
    deleted_at = Column(
        DateTime, 
        nullable=True,
        index=True,
        comment='删除时间（软删除）'
    )
    
    # 由于是视图，不定义关系，直接使用视图返回的字段
    # 以下关系定义可能不再需要，但保留以备参考
    # user = relationship('User', back_populates='videos')
    # category = relationship('Category', back_populates='videos')
    # comments = relationship('Comment', back_populates='video', cascade='all, delete-orphan')
    # likes = relationship('Like', back_populates='video', cascade='all, delete-orphan')
    # favorites = relationship('Favorite', back_populates='video', cascade='all, delete-orphan')
    
    # 索引
    __table_args__ = (
        Index('idx_status', 'status'),
        Index('idx_view_count', 'view_count'),
        Index('idx_like_count', 'like_count'),
        Index('idx_user_id', 'user_id'),
        Index('idx_category_id', 'category_id'),
    )
    
    @classmethod
    def get_recommended_videos(cls, db, page: int = 1, page_size: int = 20, skip_init_zset: bool = False) -> Tuple[List[Dict[str, Any]], int]:
        """
        获取推荐视频列表
        基于收藏数排序
        """
        try:
            print(f"获取推荐视频: page={page}, page_size={page_size}")
            # 暂时跳过缓存，直接从数据库获取
            
            # 查询总数
            count_query = text("""
                SELECT COUNT(*) FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL
            """)
            total = db.session.execute(count_query).scalar() or 0
            print(f"推荐视频总数: {total}")
            
            # 从数据库查询视频数据
            query = text("""
                SELECT * FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL 
                ORDER BY collect_count DESC, created_at DESC 
                LIMIT :limit OFFSET :offset
            """)
            
            offset = (page - 1) * page_size
            print(f"查询参数: limit={page_size}, offset={offset}")
            videos = db.session.execute(query, {'limit': page_size, 'offset': offset}).fetchall()
            print(f"查询到 {len(videos)} 个推荐视频")
            
            # 格式化结果
            result = cls._format_video_list(videos)
            print(f"格式化后推荐视频数量: {len(result)}")
            
            return result, total
        except Exception as e:
            print(f"获取推荐视频失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return []
    
    @classmethod
    def get_latest_videos(cls, db, page: int = 1, page_size: int = 20) -> Tuple[List[Dict[str, Any]], int]:
        """
        获取最新视频列表
        基于创建时间排序
        """
        try:
            print(f"获取最新视频: page={page}, page_size={page_size}")
            # 暂时跳过缓存，直接从数据库获取
            
            # 查询总数
            count_query = text("""
                SELECT COUNT(*) FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL
            """)
            total = db.session.execute(count_query).scalar() or 0
            print(f"最新视频总数: {total}")
            
            # 从数据库查询视频数据
            query = text("""
                SELECT * FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL 
                ORDER BY id DESC 
                LIMIT :limit OFFSET :offset
            """)
            
            offset = (page - 1) * page_size
            print(f"查询参数: limit={page_size}, offset={offset}")
            videos = db.session.execute(query, {'limit': page_size, 'offset': offset}).fetchall()
            print(f"查询到 {len(videos)} 个最新视频")
            
            # 格式化结果
            result = cls._format_video_list(videos)
            print(f"格式化后最新视频数量: {len(result)}")
            
            return result, total
        except Exception as e:
            print(f"获取最新视频失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return []
    
    @classmethod
    def get_hot_videos(cls, db, page: int = 1, page_size: int = 20, skip_init_zset: bool = False) -> Tuple[List[Dict[str, Any]], int]:
        """
        获取热门视频列表
        基于观看次数排序
        """
        try:
            print(f"获取热门视频: page={page}, page_size={page_size}")
            # 暂时跳过缓存，直接从数据库获取
            
            # 查询总数
            count_query = text("""
                SELECT COUNT(*) FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL
            """)
            total = db.session.execute(count_query).scalar() or 0
            print(f"热门视频总数: {total}")
            
            # 从数据库查询视频数据
            query = text("""
                SELECT * FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL 
                ORDER BY view_count DESC, created_at DESC 
                LIMIT :limit OFFSET :offset
            """)
            
            offset = (page - 1) * page_size
            print(f"查询参数: limit={page_size}, offset={offset}")
            videos = db.session.execute(query, {'limit': page_size, 'offset': offset}).fetchall()
            print(f"查询到 {len(videos)} 个热门视频")
            
            # 格式化结果
            result = cls._format_video_list(videos)
            print(f"格式化后热门视频数量: {len(result)}")
            
            # 暂时跳过Redis观看次数更新
            
            return result, total
        except Exception as e:
            print(f"获取热门视频失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return []
    
    @classmethod
    def get_slide_videos(cls, db, limit: int = 5) -> List[Dict[str, Any]]:
        """
        获取幻灯片视频
        """
        try:
            # 生成缓存键
            cache_key = RedisKeys.VIDEOS_SLIDE_CACHE
            
            # 尝试从缓存获取
            if redis_utils.redis_client:
                cached_result = redis_utils.getCache(cache_key)
                if cached_result:
                    # 添加缓存标识
                    if isinstance(cached_result, dict):
                        cached_result['fromCache'] = True
                    return cached_result
            
            # 从数据库查询幻灯片视频
            query = text("""
                SELECT * FROM videos_detail 
                WHERE status = 1 AND deleted_at IS NULL 
                ORDER BY like_count DESC, created_at DESC 
                LIMIT :limit
            """)
            
            videos = db.session.execute(query, {'limit': limit}).fetchall()
            
            # 格式化结果
            result = cls._format_video_list(videos)
            
            # 更新Redis中的观看次数
            if redis_utils.redis_client:
                for video in result:
                    counter_key = f'video:view:{video["id"]}'
                    view_count = redis_utils.redis_client.get(counter_key)
                    if view_count:
                        video['view_count'] = int(view_count)
            
            # 缓存结果
            if redis_utils.redis_client:
                redis_utils.setCache(cache_key, result, CacheExpiry.VIDEO_DETAIL)
            
            return result
        except Exception as e:
            print(f"获取幻灯片视频失败: {str(e)}")
            return []
    
    @classmethod
    def get_videos_by_category(cls, db, category_id: int, page: int = 1, page_size: int = 20) -> Tuple[List[Dict[str, Any]], int]:
        """
        按分类获取视频列表
        """
        try:
            print(f"获取分类视频: category_id={category_id}, page={page}, page_size={page_size}")
            # 暂时跳过缓存，直接从数据库获取
            
            # 查询总数
            count_query = text("""
                SELECT COUNT(*) FROM videos_detail 
                WHERE category_id = :category_id AND status = 1 AND deleted_at IS NULL
            """)
            total = db.session.execute(count_query, {'category_id': category_id}).scalar() or 0
            print(f"分类视频总数: {total}")
            
            # 从数据库查询视频数据
            query = text("""
                SELECT * FROM videos_detail 
                WHERE category_id = :category_id AND status = 1 AND deleted_at IS NULL 
                ORDER BY created_at DESC 
                LIMIT :limit OFFSET :offset
            """)
            
            offset = (page - 1) * page_size
            print(f"查询参数: category_id={category_id}, limit={page_size}, offset={offset}")
            videos = db.session.execute(query, {'category_id': category_id, 'limit': page_size, 'offset': offset}).fetchall()
            print(f"查询到 {len(videos)} 个分类视频")
            
            # 格式化结果
            result = cls._format_video_list(videos)
            print(f"格式化后分类视频数量: {len(result)}")
            
            return result, total
        except Exception as e:
            print(f"获取分类视频失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return []
    
    @classmethod
    def get_video_detail(cls, db, video_id: int) -> Optional[Dict[str, Any]]:
        """
        获取视频详情
        """
        try:
            # 生成缓存键
            cache_key = RedisKeys.VIDEO_DETAIL.format(video_id=video_id)
            
            # 尝试从缓存获取
            if redis_utils.redis_client:
                cached_result = redis_utils.getCache(cache_key)
                if cached_result:
                    # 添加缓存标识
                    if isinstance(cached_result, dict):
                        cached_result['fromCache'] = True
                    # 异步更新观看次数
                    cls.update_view_count(db, video_id)
                    return cached_result
            
            # 从数据库查询视频详情
            query = text("""
                SELECT * FROM videos_detail 
                WHERE id = :video_id AND status = 1 AND deleted_at IS NULL
            """)
            
            video = db.session.execute(query, {'video_id': video_id}).fetchone()
            
            if not video:
                return None
            
            # 格式化结果
            result = cls._format_video_dict(video)
            
            # 如果Redis可用，更新视频的观看次数为Redis中的最新值
            if redis_utils.redis_client:
                counter_key = f'video:view:{video_id}'
                view_count = redis_utils.redis_client.get(counter_key)
                if view_count:
                    result['view_count'] = int(view_count)
            
            # 缓存结果
            if redis_utils.redis_client:
                redis_utils.setCache(cache_key, result, CacheExpiry.VIDEO_DETAIL)
            
            # 异步更新观看次数
            cls.update_view_count(db, video_id)
            
            return result
        except Exception as e:
            print(f"获取视频详情失败: {str(e)}")
            return None
    
    @classmethod
    def update_view_count(cls, db, video_id: int) -> bool:
        """
        更新视频观看次数
        """
        try:
            # 优先使用Redis增加计数
            if redis_utils.redis_client:
                # 使用Redis增加计数
                counter_key = f'video:view:{video_id}'
                redis_utils.redis_client.incr(counter_key)
                # 设置过期时间为24小时
                redis_utils.redis_client.expire(counter_key, CacheExpiry.VIDEO_VIEW_COUNT)
                return True
            else:
                # 如果Redis不可用，直接更新数据库
                query = "UPDATE videos SET view_count = view_count + 1 WHERE id = :video_id AND status = 1"
                result = db.session.execute(query, {'video_id': video_id})
                db.session.commit()
                return result.rowcount > 0
        except Exception as e:
            if 'db' in locals():
                db.session.rollback()
            print(f"更新视频观看次数失败: {str(e)}")
            return False
    
    @classmethod
    def get_total_videos(cls, db, category_id: Optional[int] = None) -> int:
        """
        获取视频总数
        """
        try:
            # 生成缓存键
            cache_key = RedisKeys.VIDEO_COUNT.format(category_id=category_id or "all")
            
            # 尝试从缓存获取
            if redis_utils.redis_client:
                cached_count = redis_utils.getCache(cache_key)
                if cached_count is not None:
                    return cached_count
            
            # 从数据库查询
            if category_id is not None:
                query = "SELECT COUNT(*) FROM videos_detail WHERE status = 1 AND deleted_at IS NULL AND category_id = :category_id"
                result = db.session.execute(query, {'category_id': category_id}).scalar()
            else:
                query = "SELECT COUNT(*) FROM videos_detail WHERE status = 1 AND deleted_at IS NULL"
                result = db.session.execute(query).scalar()
            
            count = result or 0
            
            # 缓存结果
            if redis_utils.redis_client:
                redis_utils.setCache(cache_key, count, CacheExpiry.VIDEO_COUNT)
            
            return count
        except Exception as e:
            print(f"获取视频总数失败: {str(e)}")
            return 0
    
    @classmethod
    def refresh_all_caches(cls, db) -> None:
        """
        刷新所有相关缓存
        用于视频发布、修改、删除时
        """
        try:
            redis_client = get_client()
            
            if redis_client:
                # 删除所有视频相关缓存
                patterns = [
                    RedisKeys.VIDEO_RECOMMENDED_ZSET + '*',
                    RedisKeys.VIDEO_LATEST_PAGE + '*',
                    RedisKeys.VIDEO_HOT_ZSET + '*',
                    RedisKeys.VIDEO_SLIDE + '*',
                    RedisKeys.VIDEO_CATEGORY_PAGE + '*',
                    RedisKeys.VIDEO_DETAIL.format(video_id='*'),
                    RedisKeys.VIDEO_COUNT.format(category_id='*')
                ]
                
                for pattern in patterns:
                    delete_cache_pattern(pattern)
                
                print("视频相关缓存已刷新")
        except Exception as e:
            print(f"刷新视频缓存失败: {str(e)}")
    
    @classmethod
    def _format_video_list(cls, videos) -> List[Dict[str, Any]]:
        """
        格式化视频列表数据
        """
        result = []
        for video in videos:
            video_dict = cls._format_video_dict(video)
            result.append(video_dict)
        return result
    
    @classmethod
    def _format_video_dict(cls, video) -> Dict[str, Any]:
        """
        格式化单个视频数据
        """
        video_dict = dict(video._mapping)
        
        # 解析JSON字段
        try:
            video_dict['tags'] = json.loads(video_dict.get('tags', '[]')) if video_dict.get('tags') else []
        except (json.JSONDecodeError, TypeError):
            video_dict['tags'] = []
            
        try:
            video_dict['actors'] = json.loads(video_dict.get('actors', '[]')) if video_dict.get('actors') else []
        except (json.JSONDecodeError, TypeError):
            video_dict['actors'] = []
        
        # 格式化时间
        if video_dict.get('create_time'):
            video_dict['create_time'] = video_dict['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        
        return video_dict